﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using AlexandriaPressAdminObject;

namespace AlexandriaPressAdminDataAccessLayer
{
    public class AuthorDL
    {
        private static string myConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["BookConn1"].ConnectionString;
            }
        }

        public static List<AuthorInfo> displayAll()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_View_Author_List";

            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<AuthorInfo> lstOfAuthor = new List<AuthorInfo>();
            AuthorInfo Author = null;

            while (dtReader.Read())
            {
                Author = new AuthorInfo();
                Author.ID = dtReader.GetInt32(dtReader.GetOrdinal("UserID"));
                Author.Name = dtReader.GetString(dtReader.GetOrdinal("Full Name"));
                Author.Email = dtReader.GetString(dtReader.GetOrdinal("Email"));
                Author.DateJoined = dtReader.GetDateTime(dtReader.GetOrdinal("DateJoined"));
                Author.ProfilePic = "~/MasterPages/UserPages/" + dtReader.GetString(dtReader.GetOrdinal("ProfilePicture"));
                lstOfAuthor.Add(Author);
            }
            return lstOfAuthor;
        }

        public static int suspendAuthor(List<AuthorInfo> _authorList)
        {
            int count = 0;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            if (conn.State != ConnectionState.Open) conn.Open();
            
             SqlTransaction transaction = conn.BeginTransaction();

             try
             {
                 foreach (AuthorInfo author in _authorList)
                 {
                     SqlCommand SuspendAuthor = new SqlCommand();
                     SuspendAuthor.Connection = conn;
                     SuspendAuthor.CommandType = CommandType.StoredProcedure;
                     SuspendAuthor.CommandText = "usp_Suspend_User_Author";
                     SuspendAuthor.Transaction = transaction;

                     SqlParameter param1 = new SqlParameter();
                     param1.DbType = DbType.Int32;
                     param1.Value = author.ID;
                     param1.ParameterName = "@userId";
                     param1.Direction = ParameterDirection.Input;
                     SuspendAuthor.Parameters.Add(param1);

                     SqlParameter param2 = new SqlParameter();
                     param2.DbType = DbType.String;
                     param2.Value = author.Reason;
                     param2.ParameterName = "@reason";
                     param2.Direction = ParameterDirection.Input;
                     SuspendAuthor.Parameters.Add(param2);

                     SqlParameter param3 = new SqlParameter();
                     param3.DbType = DbType.DateTime;
                     param3.Value = author.EndOfSuspension;
                     param3.ParameterName = "@endSuspensionDate";
                     param3.Direction = ParameterDirection.Input;
                     SuspendAuthor.Parameters.Add(param3);

                     int a = Convert.ToInt32(SuspendAuthor.ExecuteScalar());
                     count++;
                 }
                 transaction.Commit();
                 return count;
             }
             catch
             {
                 transaction.Rollback();
                 return 0;
             }

             finally 
             {
                 conn.Close();
                 transaction.Dispose();
             }
        }

        public static int banAuthor(List<AuthorInfo> _authorList)
        {
            int count = 0;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            if (conn.State != ConnectionState.Open) conn.Open();


               SqlTransaction transaction = conn.BeginTransaction();

               try
               {
                   foreach (AuthorInfo author in _authorList)
                   {
                       SqlCommand BanAuthor = new SqlCommand();
                       BanAuthor.Connection = conn;
                       BanAuthor.CommandType = CommandType.StoredProcedure;
                       BanAuthor.CommandText = "usp_Ban_User_Author";
                       BanAuthor.Transaction = transaction;

                       SqlParameter param1 = new SqlParameter();
                       param1.DbType = DbType.Int32;
                       param1.Value = author.ID;
                       param1.ParameterName = "@userId";
                       param1.Direction = ParameterDirection.Input;
                       BanAuthor.Parameters.Add(param1);

                       SqlParameter param2 = new SqlParameter();
                       param2.DbType = DbType.String;
                       param2.Value = author.Reason;
                       param2.ParameterName = "@reasonForBan";
                       param2.Direction = ParameterDirection.Input;
                       BanAuthor.Parameters.Add(param2);


                       count++;
                       int a = Convert.ToInt32(BanAuthor.ExecuteScalar());
                   }
                   transaction.Commit();
                   return count;
               }
               catch
               {
                   transaction.Rollback();
                   return 0;
               }
               finally 
               {
                   conn.Close();
                   transaction.Dispose();
               }



        }
    }
}
